package com.kandinfo.isbnmanage.repository.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.kandinfo.base.common.enumeration.EErrorCode;
import com.kandinfo.base.common.enums.EDataSource;
import com.kandinfo.base.common.exception.BaseException;
import com.kandinfo.base.common.util.DBUtil;
import com.kandinfo.isbnmanage.dto.IsbnDigDto;
/**
 * 
 * @author weiwang
 */
@Repository
public class IsbnDigSearchRepository{

	public List<IsbnDigDto> getIsbnDigDtoList() {
		List<IsbnDigDto> list = new ArrayList<IsbnDigDto>();
		DBUtil db = new DBUtil(EDataSource.Homework_2.getProvinceId());
		StringBuffer nativeSql = new StringBuffer();
        nativeSql.append(" SELECT ");
        nativeSql.append("     uur.barcode AS barcode ");
        nativeSql.append(" 	   ,COUNT(DISTINCT uur.user_id) AS userCount ");
        nativeSql.append("     ,GROUP_CONCAT(DISTINCT uur.province) AS provinces ");
        nativeSql.append("     ,min(uur.create_ts) AS firstUploadTime ");
        nativeSql.append(" FROM ");
        nativeSql.append("     user_upload_record uur ");
        nativeSql.append(" WHERE ");
        nativeSql.append("     uur.type = 2 ");
        nativeSql.append("     and not exists( ");
        nativeSql.append("     select 1 from homework h where h.barcode = uur.barcode) ");
//        nativeSql.append("     and not exists( ");
//        nativeSql.append("     select 1 from isbn_manage im where im.barcode = uur.barcode) ");
        nativeSql.append(" GROUP BY uur.barcode ");
        nativeSql.append(" HAVING  ");
        nativeSql.append(" userCount > 1 ");
        ResultSet rs = null;
        try{
        	rs = db.exeQuery(nativeSql.toString(), null);
        	while(rs.next()){
        		IsbnDigDto i = new IsbnDigDto();
        		i.setBarcode(rs.getString("barcode"));
        		i.setFirstUploadTime(rs.getDate("firstUploadTime"));
        		i.setProvinces(rs.getString("provinces"));
        		i.setUserCount(rs.getInt("userCount"));
        		list.add(i);
        	}
        }catch(SQLException e){
        	throw new BaseException(EErrorCode.SQL_EXCEPTION);
        }finally{
        	db.releaseConnection();
        }
		return list;
	}

}
